package com.betterjr.modules.loan.dao;

import java.math.BigDecimal;
import java.util.List;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Select;

import com.betterjr.common.annotation.BetterjrMapper;
import com.betterjr.mapper.common.Mapper;
import com.betterjr.modules.loan.entity.ScfRequest;

@BetterjrMapper
public interface ScfRequestMapper extends Mapper<ScfRequest> {

    /*
     * 查询未来回款统计
     */
    @Select("select sum(F_LOAN_BALANCE)/10000 as monthTotalBalance from t_scf_request where C_LAST_STATUS='4' and C_CUST_TYPE='1' and D_END_DATE between #{startDate} and #{endDate}")
    @ResultType(BigDecimal.class)
    BigDecimal statisticEveryMonthRepayment(@Param("startDate") String startDate, @Param("endDate") String endDate);

    /*
     * 查出付款表有没有存在融资的还款编号
     */
    @Select("select count(C_REQUESTNO) from t_scf_pay_plan where L_FACTORNO=#{factorNo}")
    @ResultType(String.class)
    String existRequestNo(@Param("factorNo") String requestNo);

    /*
     * 得到融资编号
     */
    @Select("select C_REQUESTNO from t_scf_request where C_LAST_STATUS=#{lastStatus} and C_CUST_TYPE=#{custType}")
    @ResultType(String.class)
    List<String> querySupplyRequestNoList(@Param("lastStatus") String lastStatus, @Param("custType") String custType);

    /*
     * 得到核心企业未来付款的所有融资编号
     */
    @Select("select C_REQUESTNO from t_scf_request where C_LAST_STATUS=#{lastStatus} and L_CORE_CUSTNO=#{coreCustNo}")
    @ResultType(String.class)
    List<String> queryCoreEnterpriseRequestNoList(@Param("lastStatus") String lastStatus,
            @Param("coreCustNo") String coreCustNo);

    /*
     * 统计资金方未来每月回款总金额
     */
    @Select("select sum(F_LOAN_BALANCE)/10000 as monthTotalBalance from t_scf_request where L_FACTORNO=#{factorNo} and C_LAST_STATUS='4' and D_END_DATE between #{startDate} and #{endDate}")
    @ResultType(BigDecimal.class)
    BigDecimal StatisFutrueEveryMonthPayBalance(@Param("factorNo") String factorNo,
            @Param("startDate") String startDate, @Param("endDate") String endDate);

    /*
     * 统计核心企业未来每月付款总金额
     */
    @Select("select sum(F_LOAN_BALANCE)/10000 as monthTotalBalance from t_scf_request where L_CORE_CUSTNO=#{coreCustNo} and C_LAST_STATUS='4' and D_END_DATE between #{startDate} and #{endDate}")
    @ResultType(BigDecimal.class)
    BigDecimal statisCoreEnterpriseFutrueEveryMonthPayBalance(@Param("coreCustNo") String coreCustNo,
            @Param("startDate") String startDate, @Param("endDate") String endDate);

    /*
     * 查询核心企业未来付款情况统计
     */
    @Select("select sum(F_SHOULD_TOTAL_BALANCE)/10000 as monthTotalBalance from t_scf_pay_plan where C_REQUESTNO=#{requestNo} and D_PLAN_DATE between #{startDate} and #{endDate}")
    @ResultType(BigDecimal.class)
    BigDecimal statisticFutureEveryMonthpayment(@Param("requestNo") String requestNo,
            @Param("startDate") String startDate, @Param("endDate") String endDate);

    /*
     * 过去资金方放款及回款情况统计——回款
     */
    @Select("select sum(F_BALANCE)/10000 as monthTotalBalance from t_scf_request where C_CUST_TYPE='1' and C_LAST_STATUS='3' and T_REQUEST_TIME between #{startDate} and #{endDate}")
    @ResultType(BigDecimal.class)
    BigDecimal statisticBeforeEveryMonthFinanceSupplyBalance(@Param("startDate") String startDate,
            @Param("endDate") String endDate);

    /*
     * 过去资金方放款及回款情况统计——放款
     */
    @Select("select sum(F_LOAN_BALANCE)/10000 as monthTotalBalance from t_scf_request where C_CUST_TYPE='1' and C_LAST_STATUS='3' and D_ACTUAL_DATE between #{startDate} and #{endDate}")
    @ResultType(BigDecimal.class)
    BigDecimal statisticBeforeEveryMonthFinanceLoadBalance(@Param("startDate") String startDate,
            @Param("endDate") String endDate);

    /*
     * 过去供应商融资情况——申请金额
     */
    @Select("select sum(F_BALANCE)/10000 as monthTotalBalance from t_scf_request where C_LAST_STATUS in('1','2') and "
            + "L_CUSTNO=#{custNo} and D_REQUEST_DATE between #{startDate} and #{endDate}")
    @ResultType(BigDecimal.class)
    BigDecimal statisticSupplyBeyondEveryMonthpayment(@Param("startDate") String startDate,
            @Param("endDate") String endDate, @Param("custNo") String custNo);

    /*
     * 过去供应商融资情况——放款金额
     */
    @Select("select sum(F_LOAN_BALANCE)/10000 as monthTotalBalance from t_scf_request where C_LAST_STATUS in('4','5','6','7','8') and "
            + "L_CUSTNO=#{custNo} and D_ACTUAL_DATE between #{startDate} and #{endDate}")
    @ResultType(BigDecimal.class)
    BigDecimal statisticSupplyBeyondEveryMonthLoadBalance(@Param("startDate") String startDate,
            @Param("endDate") String endDate, @Param("custNo") String custNo);

}